Spreadsheets are a common and powerful software tool. Business analysts and others use spreadsheets for purposes beyond the original design and intent of spreadsheets. Spreadsheets provide a familiar environment in which forms of tabular data are created and maintained with the user taking advantage of authoring tools to structure data. Decision Tables are a tabular form of expressing logic. One axis defines predicates and consequences and the other axis defines sets of predicates and consequences; each set of which is considered a discrete logical decision. Cells within the table define values for the predicates and consequences. Users can merge cells for organizational or other purposes to provide or indicate particular meaning to the operation of the decision table.
Decision Tables are frequently defined in spreadsheets and hence may fall into the category of spreadsheet misuse as a way to inject functionality into a spreadsheet for which the spreadsheet was not originally intended. When creating a decision table, a user typically explicitly defines appropriate columns and rows and manages merging of cells to achieve a required result with no assistance from the tool. When a user wishes to update, sort, or modify a decision table created in this way, the process is often laborious, error-prone, and time consuming, because the user should manually unmerge, fill in, sort, and/or update cells, as set forth below.
An illustrative example decision table is shown in Table 1, below.
TABLE 1Policy typeApplicant agePremiumComprehensive<18100010 <= age < 2575025 <= age < 40500Third Party, Fire & Theft<1875018 <= age < 2550025 <= age < 40250Third Party<1850018 <= age < 2525025 <= age < 4050
Tables 2-5, below, illustrate operations on the example decision table. This decision table is illustrated as an arrangement of merged and non-merged cells in a spreadsheet which represent a model of logic. In this example the logic represented is a simplified insurance premium calculator, but other more complex logic can also be represented in a similar manner. More complex decision tables can span one or more pages in a spreadsheet. The various policy types in the first column of this example decision table are “Comprehensive”, “Third Party, Fire & Theft”, and “Third Party”. Each of the policy type cells is merged to cover three different cells in the second column representing different applicant ages, “<18”, “18<=age <25”, and “25<=age 40”. The third column of cells shows premiums corresponding to each applicant age for each policy type. If a user wants to update all premiums for applicants younger than 18 irrespective of policy type, the user should manually locate each row where the age is less than 18 and update the premium value. The user can alternately use sort and merge functions in a spreadsheet program to simplify the process, but many components of the process are still laborious. The user should remove the merging from the cells, complete blank cells, sort the cells, and update the desired cells, as shown in Tables 2-5.
TABLE 2Policy typeApplicant agePremiumComprehensive<18100010 <= age < 2575025 <= age < 40500Third Party, Fire & Theft<1075018 <= age < 2550025 <= age < 40250Third Party<1850018 <= age < 2525025 <= age < 4050
Table 2 illustrates removing merging from the first example decision table. The cells in the first column which were merged in Table 1 are unmerged, separating them into individual cells. However, the unmerged cells which are not the first cell are blank. For example, when a cell that has been created by merging 3 cells is subsequently unmerged, the first cell retains the value, and the remaining cells are blank, or do not contain any value. In order to correct this problem, the user should populate those blank cells with the proper values reflecting their previously merged state in order to maintain order and data consistency in the spreadsheet.
TABLE 3Policy typeApplicant agePremiumComprehensive<181000Comprehensive18 <= age < 25750Comprehensive25 <= age < 40500Third Party, Fire & Theft<18750Third Party, Fire & Theft18 <= age < 25500Third Party, Fire & Theft25 <= age < 40250Third Party<18500Third Party18 <= age < 25250Third Party25 <= age < 4050
Table 3 illustrates completing the resulting blank cells in the example decision table as shown in Table 2. In this case, the user manually enters the text in each blank cell that corresponds to its previously merged state.
TABLE 4Policy typeApplicant agePremiumComprehensive<181000Third Party, Fire & Theft<18750Third Party<18500Comprehensive18 <= age < 25750Third Party, Fire & Theft18 <= age < 25500Third Party18 <= age < 25250Comprehensive25 <= age < 40500Third Party, Fire & Theft25 <= age < 40250Third Party25 <= age < 4050
Table 4 illustrates sorting cells in the decision table as shown in Table 3. In this case, the user wants to view all the premiums for the age bracket <18. The user sorts the cells by the applicant age column so that all the cells corresponding to the applicant age are in at the top of the column.
TABLE 5Policy typeApplicant agePremiumComprehensive<181500Third Party, Fire & Theft<181500Third Party<181500Comprehensive18 <= age < 25750Third Party, Fire & Theft18 <= age < 25500Third Party18 <= age < 25250Comprehensive25 <= age < 40500Third Party, Fire & Theft25 <= age < 40250Third Party25 <= age < 4050
Table 5 illustrates updating cells in the decision table as shown in Table 4. In this case, the user enters 1500 for each cell where the applicant age is <18. Then, the user should undo all or part of those steps to restore the functionality of the decision table in the spreadsheet. This process is laborious, error prone, and time consuming.